/**
 * 
 */
package com.googlecode.rotools.rodb;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;

/**
 * 
 */
class RODBImpl implements RODB{
	private static final String SELECT_ITEM_FOR_UPDATE = "select * from ITEM where ITEM_ID=? for update";
	private static final String SELECT_ITEM_ID = "select * from ITEM where ITEM_ID=?";
	private static final String SELECT_ITEM_ALL = "select * from ITEM";
	private static final String SELECT_ITEM_COUNT = "select count(*) from ITEM";
	
	private static final String INSERT_ITEM = "insert into ITEM(" +
			"ITEM_ID, " +
			"ITEM_NAME, " +
			"ITEM_UNKNOWN_NAME, " +
			"ITEM_DESCRIPTION, " +
			"ITEM_UNKNOWN_DESCRIPTION, " +
			"ITEM_SLOT, " +
			"WEAPON_LEVEL, " +
			"WEAPON_ATK, " +
			"GUARD_DEF, " +
			"SLOT_COUNT, " +
			"CARD_PREFIX" + ") values (" +
			"?,?,?,?,?,?,?,?,?,?,?" + 
			")";
	
	private static final String UPDATE_ITEM = "update ITEM set " + 
			"ITEM_ID=?, " +
			"ITEM_NAME=?, " +
			"ITEM_UNKNOWN_NAME=?, " +
			"ITEM_DESCRIPTION=?, " +
			"ITEM_UNKNOWN_DESCRIPTION=?, " +
			"ITEM_SLOT=?, " +
			"WEAPON_LEVEL=?, " +
			"WEAPON_ATK=?, " +
			"GUARD_DEF=?, " +
			"SLOT_COUNT=?, " +
			"CARD_PREFIX=? " + 
			"where ITEM_ID=?";
	
	private static final String DELETE_ALL_ITEMS = "delete from ITEM";
	
	private Connection connection;
	
	/**
	 * @param connection
	 */
	RODBImpl(Connection connection) {
		super();
		this.connection = connection;
	}

	/* (non-Javadoc)
	 * @see com.googlecode.rotools.rodb.RODB#getItem(java.lang.String)
	 */
	public Item getItem(String id) throws SQLException {
		PreparedStatement statement = connection.prepareStatement(SELECT_ITEM_ID);
		try{
			SqlUtil.setString(statement, 1, id);
			ResultSet resultSet = statement.executeQuery();
			try{
				Item item = null;
				if(resultSet.next()){
					item = new ItemImpl(resultSet);
				}
				return item;
				
			}finally{
				resultSet.close();
				
			}
			
		}finally{
			statement.close();
			
		}
	}

	/* (non-Javadoc)
	 * @see com.googlecode.rotools.rodb.RODB#getItems()
	 */
	public Items getItems() throws SQLException {
		Statement statement = connection.createStatement();
		try{
			ResultSet resultSet = statement.executeQuery(SELECT_ITEM_ALL);
			return new ItemsImpl(resultSet);
			
		}finally{
			statement.close();
			
		}
	}
	public void removeAllItems() throws SQLException {
		Statement statement = connection.createStatement();
		try{
			statement.execute(DELETE_ALL_ITEMS);
			
		}finally{
			statement.close();
			
		}
	}

	/* (non-Javadoc)
	 * @see com.googlecode.rotools.rodb.RODB#getItemCount()
	 */
	public int getItemCount() throws SQLException {
		Statement statement = connection.createStatement();
		try{
			ResultSet resultSet = statement.executeQuery(SELECT_ITEM_COUNT);
			try{
				resultSet.next();
				return resultSet.getInt(1);
				
			}finally{
				resultSet.close();
			}
		}finally{
			statement.close();
			
		}
	}
	private void insert(Item[] items) throws SQLException {
		PreparedStatement statement = connection.prepareStatement(INSERT_ITEM);
		try{
			for (Item item : items) {
				SqlUtil.setString(statement, 1, item.getId());
				SqlUtil.setString(statement, 2, item.getName());
				SqlUtil.setString(statement, 3, item.getUnknownName());
				SqlUtil.setString(statement, 4, item.getDescription());
				SqlUtil.setString(statement, 5, item.getUnknownDescription());
				SqlUtil.setInteger(statement, 6, item.getSlot());
				SqlUtil.setInteger(statement, 7, item.getWeaponLevel());
				SqlUtil.setInteger(statement, 8, item.getWeaponAtk());
				SqlUtil.setInteger(statement, 9, item.getGuardDef());
				SqlUtil.setInteger(statement, 9, item.getGuardDef());
				SqlUtil.setInteger(statement, 10, item.getSlotCount());
				SqlUtil.setString(statement, 11, item.getCardPrefix());
				try{
					statement.execute();
					
				}catch(SQLException e){
					e.printStackTrace();
					
				}
			}
			
		}finally{
			statement.close();
			
		}
	}
	private void update(String id, Item item) throws SQLException {
		HashMap<String, Item> hashMap = new HashMap<String, Item>(1);
		hashMap.put(id, item);
		update(hashMap);
	}
	private void update(Map<String, Item> itemMap) throws SQLException {
		PreparedStatement statement = connection.prepareStatement(UPDATE_ITEM);
		try{
			Iterator<String> keys = itemMap.keySet().iterator();
			while (keys.hasNext()) {
				String key = (String) keys.next();
				Item item = itemMap.get(key);
				SqlUtil.setString(statement, 1, item.getId());
				SqlUtil.setString(statement, 2, item.getName());
				SqlUtil.setString(statement, 3, item.getUnknownName());
				SqlUtil.setString(statement, 4, item.getDescription());
				SqlUtil.setString(statement, 5, item.getUnknownDescription());
				SqlUtil.setInteger(statement, 6, item.getSlot());
				SqlUtil.setInteger(statement, 7, item.getWeaponLevel());
				SqlUtil.setInteger(statement, 8, item.getWeaponAtk());
				SqlUtil.setInteger(statement, 9, item.getGuardDef());
				SqlUtil.setInteger(statement, 10, item.getSlotCount());
				SqlUtil.setString(statement, 11, item.getCardPrefix());
				SqlUtil.setString(statement, 12, key);
				statement.execute();
			}
		}finally{
			statement.close();
		}
	}
	public void insertItem(Item item) throws SQLException {
		try{
			insert(new Item[]{item});
			
		}finally{
			connection.commit();
		}
	}
	public void updateItem(String id, Item item) throws SQLException {
		try{
			update(id, item);
			
		}finally{
			connection.commit();
		}
	}
	public void updateItem(Item item, boolean create) throws SQLException {
		PreparedStatement statement = connection.prepareStatement(SELECT_ITEM_FOR_UPDATE);
		try{
			try{
				SqlUtil.setString(statement, 1, item.getId());
				ResultSet resultSet = statement.executeQuery();
				try{
					if(resultSet.next()){
						update(item.getId(), item);
						
					} else {
						if(!create){
							throw new SQLException("id = " + item.getId());
						}
						insert(new Item[]{item});
						
					}
					
				}finally{
					resultSet.close();
					
				}
				
			}finally{
				statement.close();
				
			}
		}finally{
			connection.commit();
			
		}
	}

	/* (non-Javadoc)
	 * @see java.io.Closeable#close()
	 */
	public void close() throws IOException {
		try{
			connection.close();
			
		}catch(SQLException exception){
			throw new IOException(exception);
			
		}
	}
	/* (non-Javadoc)
	 * @see com.googlecode.rotools.rodb.RODB#getConnection()
	 */
	public Connection getConnection() {
		return connection;
	}
}
